using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;
using System.Collections.Generic;
using Pms.Utils;
using Biblioteca.Utils;
using Biblioteca.DataBase;
/*
 * 
 * Autor      : Jose Jornando de Carvalho Junior    
 * Data       : 10/04/2010 07:08:21
 * Descricao  : 
 * Alteracoes : 
 * 
 */

namespace SERIX
{
    public class CRespostasAbertas
    {
        private string IDT_QST;
        private string IDT_TIQ;
        private string IDT_CAT;
        private string IDT_QUE;
        private string RESPOSTA;

        /// <sumary>
        /// Construtor da classe respostas_abertas
        /// <sumary>
        public CRespostasAbertas()
        {

        }

        /// <sumary>
        /// Construtor da classe Respostas_abertas
        /// <sumary>
        public CRespostasAbertas(string IDT_QST, string IDT_TIQ, string IDT_CAT, string IDT_QUE, string RESPOSTA)
        {
            this.IDT_QST = IDT_QST;
            this.IDT_TIQ = IDT_TIQ;
            this.IDT_CAT = IDT_CAT;
            this.IDT_QUE = IDT_QUE;
            this.RESPOSTA = RESPOSTA;
        }

        #region Propriedades
        /// <sumary>
        /// Identificador do questionario
        /// <sumary>
        public string Idt_qst
        {
            get
            {
                return this.IDT_QST;
            }
            set
            {
                this.IDT_QST = value;
            }
        }
        /// <sumary>
        /// Identificador do tipo de questionario
        /// <sumary>
        public string Idt_tiq
        {
            get
            {
                return this.IDT_TIQ;
            }
            set
            {
                this.IDT_TIQ = value;
            }
        }
        /// <sumary>
        /// Identificador da categoria do questionario
        /// <sumary>
        public string Idt_cat
        {
            get
            {
                return this.IDT_CAT;
            }
            set
            {
                this.IDT_CAT = value;
            }
        }
        /// <sumary>
        /// Identificador da questao
        /// <sumary>
        public string Idt_que
        {
            get
            {
                return this.IDT_QUE;
            }
            set
            {
                this.IDT_QUE = value;
            }
        }
        /// <sumary>
        /// Resposta aberta
        /// <sumary>
        public string Resposta
        {
            get
            {
                return this.RESPOSTA;
            }
            set
            {
                this.RESPOSTA = value;
            }
        }
        #endregion Propriedades

        /// <summary>
        /// Inserindo um novo Respostas_abertas.
        /// </summary>
        public void Inserir(string Login)
        {
            OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"insert into Serix.SRX_RESPOSTAS_ABERTAS
			                  ( IDT_QST, IDT_TIQ, IDT_CAT, IDT_QUE, RESPOSTA )
						   values
						      ( :IDT_QST, :IDT_TIQ, :IDT_CAT, :IDT_QUE, :RESPOSTA )";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                command.Parameters.Add(new OracleParameter("IDT_QST", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_TIQ", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_CAT", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_QUE", OracleType.Number));
                command.Parameters.Add(new OracleParameter("RESPOSTA", OracleType.VarChar));

                command.Parameters["IDT_QST"].Value = this.IDT_QST;
                command.Parameters["IDT_TIQ"].Value = this.IDT_TIQ;
                command.Parameters["IDT_CAT"].Value = this.IDT_CAT;
                command.Parameters["IDT_QUE"].Value = this.IDT_QUE;
                command.Parameters["RESPOSTA"].Value = this.RESPOSTA;
                command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
        }

        /// <summary>
        /// Inserindo um novo Respostas_abertas.
        /// </summary>
        public void Inserir(OracleCommand command)
        {

            string SQL = @"insert into Serix.SRX_RESPOSTAS_ABERTAS
			                  ( IDT_QST, IDT_TIQ, IDT_CAT, IDT_QUE, RESPOSTA )
						   values
						      ( :IDT_QST, :IDT_TIQ, :IDT_CAT, :IDT_QUE, :RESPOSTA )";

            try
            {
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                command.Parameters.Add(new OracleParameter("IDT_QST", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_TIQ", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_CAT", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_QUE", OracleType.Number));
                command.Parameters.Add(new OracleParameter("RESPOSTA", OracleType.VarChar));

                command.Parameters["IDT_QST"].Value = this.IDT_QST;
                command.Parameters["IDT_TIQ"].Value = this.IDT_TIQ;
                command.Parameters["IDT_CAT"].Value = this.IDT_CAT;
                command.Parameters["IDT_QUE"].Value = this.IDT_QUE;
                command.Parameters["RESPOSTA"].Value = this.RESPOSTA;
                command.ExecuteNonQuery();
                command.Parameters.Clear();
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
        }

        /// <summary>
        /// Alterando um Respostas_abertas.
        /// </summary>
        public void Alterar(string Login)
        {
            OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"update Serix.SRX_RESPOSTAS_ABERTAS set 
			                   RESPOSTA = :RESPOSTA
						   where
						       IDT_QST = :IDT_QST and IDT_TIQ = :IDT_TIQ and IDT_CAT = :IDT_CAT and IDT_QUE = :IDT_QUE ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                command.Parameters.Add(new OracleParameter("IDT_QST", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_TIQ", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_CAT", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_QUE", OracleType.Number));
                command.Parameters.Add(new OracleParameter("RESPOSTA", OracleType.VarChar));

                command.Parameters["IDT_QST"].Value = this.IDT_QST;
                command.Parameters["IDT_TIQ"].Value = this.IDT_TIQ;
                command.Parameters["IDT_CAT"].Value = this.IDT_CAT;
                command.Parameters["IDT_QUE"].Value = this.IDT_QUE;
                command.Parameters["RESPOSTA"].Value = this.RESPOSTA;
                command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
        }

        /// <summary>
        /// Exclui um  Respostas_abertas.
        /// </summary>
        public void Excluir(string Login)
        {
            Tabela tab = new Tabela("SRX_RESPOSTAS_ABERTAS", BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo));
            tab.adicionarFiltro(new Coluna("IDT_QST", DbType.Int32, this.IDT_QST));
            tab.adicionarFiltro(new Coluna("IDT_TIQ", DbType.Int32, this.IDT_TIQ));
            tab.adicionarFiltro(new Coluna("IDT_CAT", DbType.Int32, this.IDT_CAT));
            tab.adicionarFiltro(new Coluna("IDT_QUE", DbType.Int32, this.IDT_QUE));
            tab.delete();
        }

        static public DataTable ObterTodos(string login)
        {

            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_RESPOSTAS_ABERTAS order by RESPOSTA ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;

            }
            catch (Exception e)
            {

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }

        static public CRespostasAbertas PesquisaporCodigo(string idt_qst, string idt_tiq, string idt_cat, string idt_que, string login)
        {
            CRespostasAbertas obj;
            string SQL = @"SELECT *
                            FROM  Serix.SRX_RESPOSTAS_ABERTAS 
                            WHERE IDT_QST = :IDT_QST and IDT_TIQ = :IDT_TIQ and IDT_CAT = :IDT_CAT and IDT_QUE = :IDT_QUE";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
                qy.AdicionarParametro(":IDT_QST", DbType.String, idt_qst);
                qy.AdicionarParametro(":IDT_TIQ", DbType.String, idt_tiq);
                qy.AdicionarParametro(":IDT_CAT", DbType.String, idt_cat);
                qy.AdicionarParametro(":IDT_QUE", DbType.String, idt_que);

                qy.Executar();
                obj = new CRespostasAbertas(qy.ObterCampo("IDT_QST"), qy.ObterCampo("IDT_TIQ"), qy.ObterCampo("IDT_CAT"), qy.ObterCampo("IDT_QUE"), qy.ObterCampo("RESPOSTA"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return new CRespostasAbertas();
            }
            finally
            {
                qy.FecharConexao();
            }
            return obj;
        }

        static public void ExcluirTodasRespostas(string idt_qst, OracleCommand command)
        {
            string SQL = @"delete Serix.SRX_RESPOSTAS_ABERTAS 
                            WHERE IDT_QST = :IDT_QST";
            try
            {
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                command.Parameters.Add(new OracleParameter("IDT_QST", OracleType.Number));
                command.Parameters["IDT_QST"].Value = idt_qst;
                command.ExecuteNonQuery();
                command.Parameters.Clear();
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
        }

        
    }
}

